﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using UniversityStudentManagemnetSystem.DAO.DAL;

namespace UniversityStudentManagemnetSystem.DAO.Gateway
{
    public class StudentCourseEnrollGateway:Gateway
    {
        public Student SearchStudentByRegNo(string regNo)
        {
            Student student = new Student();
            try
            {

                Connection.Open();
                string selectQuery = string.Format("select s.Name,s.Email,s.DepartmentId,d.Name as Department from t_StudentRegistation as s inner join t_Department as d on s.DepartmentId=d.Id and s.RegNo='" + regNo + "'");
                Command.CommandText = selectQuery;
                SqlDataReader reader = Command.ExecuteReader();
                while (reader.Read())
                {
                    
                    student.Name = reader["Name"].ToString();
                    student.Email = reader["Email"].ToString();
                    student.Department.DeptId = Convert.ToInt32(reader["DepartmentId"]);
                    student.Department.Name = reader["Department"].ToString();
                    
                   
                }
                reader.Close();


            }
            catch (FormatException exception)
            {
                throw new Exception(exception.Message);
            }
            catch (Exception exception)
            {
                throw new Exception("Database Error",exception);
                
            }
            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return student;
        }

        public DataTable GetAllCoursesForSelectedDepartment(string departmentId)
        {
            try
            {

                string selectQuery = string.Format("select Id,Code from t_Course where DepartmentId='" + departmentId + "' ");
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
                DataSet myDataset = new DataSet();
                sqlDataAdapter.Fill(myDataset, "CourseTable");
                DataTable myDataTable = myDataset.Tables["CourseTable"];
                return myDataTable;
            }
            catch (Exception exception)
            {

                throw new Exception("Enroll Could not Done!", exception);
            }
           
        }

        public void SaveStudentEnrollCourses(Student aStudent)
        {
            try
            {

                Connection.Open();
                string insertQuery =
                    string.Format(
                        "insert into t_StudentEnroll values(@RegNo,@Name,@DepartmentId,@CourseId,@Email,@EnrollFlag)");
                Command.CommandText = insertQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@RegNo", SqlDbType.VarChar);
                Command.Parameters["@RegNo"].Value = aStudent.RegNo;
                Command.Parameters.Add("@Name", SqlDbType.VarChar);
                Command.Parameters["@Name"].Value = aStudent.Name;
                Command.Parameters.Add("@DepartmentId", SqlDbType.Int);
                Command.Parameters["@DepartmentId"].Value = aStudent.Department.DeptId;
                Command.Parameters.Add("@CourseId", SqlDbType.Int);
                Command.Parameters["@CourseId"].Value = aStudent.Course.CourseId;
                Command.Parameters.Add("@Email", SqlDbType.VarChar);
                Command.Parameters["@Email"].Value = aStudent.Email;
                Command.Parameters.Add("@EnrollFlag", SqlDbType.VarChar);
                Command.Parameters["@EnrollFlag"].Value = "Y";

                Command.ExecuteNonQuery();

            }
                catch(FormatException exception)
                {
                    throw new Exception(exception.Message);
                }

            catch (Exception exception)
            {
                
                throw new Exception("Enroll Could not Done!",exception);
            }
            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
        }

        public DataTable GetAllEnrollCoursesForRegNo(string regNo)
        {
            string selectQuery = string.Format("select * from view_StudentEnrollCourses where RegNo='"+regNo+"'");
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
            DataSet dataSet = new DataSet();
            sqlDataAdapter.Fill(dataSet, "EnrollTable");
            DataTable myDataTable = dataSet.Tables["EnrollTable"];
            return myDataTable;

        }

        public bool HasThisCourseEnroll(Student aStudent)
        {
            bool hasRows = false;
            try
            {

                Connection.Open();
                string selectQuery =
                    string.Format("select code from CheakEnrollCourseView where Id='" + aStudent.Course.CourseId +
                                  "' and RegNo='" + aStudent.RegNo + "' ");
                Command.CommandText = selectQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@code", SqlDbType.VarChar);
                Command.Parameters["@code"].Value = aStudent.Course.Code;
                SqlDataReader reader = Command.ExecuteReader();
                if (reader!=null)
                {
                    hasRows = reader.HasRows;
                }

            }
           catch(FormatException exception)
                {
                    throw new Exception(exception.Message);
                }
            catch (Exception exception)
            {
                
                throw new Exception("Database Error",exception);
            }
            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return hasRows;
        }

       
    }
}